﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using Workbench.Models;
using Workbench.Shared;

namespace Workbench.Persistance
{
    public class TextRepository
    {
        protected Func<SQLiteConnection> connectionProvider = null;

        internal TextRepository(Func<SQLiteConnection> connectionProvider)
        {
            this.connectionProvider = connectionProvider;
        }

        public Dictionary<TKey, UniqueWordModel> GetAllUniqueWords<TKey>(Func<UniqueWordModel, TKey> keySelector)
        {
            var result = new Dictionary<TKey, UniqueWordModel>();
            var cmdText = @"SELECT RowId, Content FROM UniqueWords";
            using (var reader = DbUtility.ExecuteReader(connectionProvider(), cmdText))
            {
                while (reader.Read())
                {
                    var index = 0;
                    var model = new UniqueWordModel();
                    model.RowId = reader.GetInt64(0);
                    index++; if (!reader.IsDBNull(index)) model.Content = reader.GetString(index);

                    result.Add(keySelector(model), model);
                }
            }
            return result;
        }

        public string GetSentenceContent(WordModel word)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@wordId", word.RowId);
            
            var cmdText =
@"SELECT SUBSTR(fc.Content, p.StartPosition + s.StartPosition + 1, s.Length) 
FROM Words w
INNER JOIN Sentences s ON w.SentenceId = s.RowId
INNER JOIN Paragraphs p ON s.ParagraphId = p.RowId
INNER JOIN Files f ON p.FileId = f.RowId
INNER JOIN FileContents fc ON f.FileContentId = fc.RowId
WHERE w.RowId = @wordId";
            return DbUtility.ExecuteScalar<string>(connectionProvider(), cmdText, parameters);
        }

        public string GetSentenceContent(long sentenceId)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("@sentenceId", sentenceId);

            var cmdText =
@"SELECT SUBSTR(fc.Content, p.StartPosition + s.StartPosition + 1, s.Length) 
FROM Sentences s
INNER JOIN Paragraphs p ON s.ParagraphId = p.RowId
INNER JOIN Files f ON p.FileId = f.RowId
INNER JOIN FileContents fc ON f.FileContentId = fc.RowId
WHERE s.RowId = @sentenceId";
            return DbUtility.ExecuteScalar<string>(connectionProvider(), cmdText, parameters);
        }

        public List<AggregatedWordModel> GetWordList(IEnumerable<ProjectFileModel> file)
        {
            var con = connectionProvider();
            var result = new List<AggregatedWordModel>();
            var cmdText =
string.Format(@"SELECT uw.Content as WordContent, bf.Content as BaseFormContent FROM Words AS w
INNER JOIN UniqueWords AS uw ON w.UniqueWordId = uw.RowId
INNER JOIN Sentences AS s ON w.SentenceId = s.RowId
INNER JOIN Paragraphs AS p ON s.ParagraphId = p.RowId 
LEFT JOIN BaseForms AS bf on w.BaseFormId = bf.RowId
WHERE p.FileId IN ({0})
ORDER BY uw.Content", string.Join(", ", file.Select(x => x.RowId)));

            using (var reader = DbUtility.ExecuteReader(con, cmdText))
            {
                var model = new AggregatedWordModel();
                while (reader.Read())
                {
                    string wordContent = null;
                    string baseFormContent = null;

                    if (!reader.IsDBNull(0)) wordContent = reader.GetString(0);
                    if (!reader.IsDBNull(1)) baseFormContent = reader.GetString(1);

                    if (wordContent != model.WordContent)
                    {
                        model = new AggregatedWordModel();
                        model.WordContent = wordContent;
                        result.Add(model);
                    }

                    model.Freq++;
                    if (baseFormContent != null)
                    {
                        model.BaseForms.Add(baseFormContent);
                    }
                }
            }
            return result;
        }

        public Dictionary<long, long> GetPOSMeasures()
        {
            Dictionary<long, long> counts = new Dictionary<long, long>(); ;

            using (var reader = DbUtility.ExecuteReader(connectionProvider(),
            "SELECT POSTypeId, COUNT(WordId) FROM Words_POSTypes GROUP BY POSTypeId;"))
            {
                long posTypeId = 0;
                long count = 0;
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) posTypeId = reader.GetInt64(0);
                    if (!reader.IsDBNull(1)) count = reader.GetInt64(1);

                    counts.Add(posTypeId, count);
                }
            }
            return counts;
        }
    }
}
